﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DealerJudge.Core;

namespace DealerJudge.Data
{
    public class SqlHelper : ISqlHelper 
    {
        private readonly ILogger _logger;
        private readonly IConnectionProvider _connectionProvider;
        
        public SqlHelper(ILogger logger, IConnectionProvider connectionProvider)
        {
            _logger = logger;
            _connectionProvider = connectionProvider;
        }
        public DataTable ExecuteDataTable(CommandType commandType, string commandText)
        {
            return ExecuteDataTable(commandType, commandText, null);
        }

        public DataTable ExecuteDataTable(CommandType commandType, string commandText, List<IDbDataParameter> parameters)
        {
            using (IDatabaseConnection dbConn = _connectionProvider.CreateDatabaseConnection())
            {
                return ExecuteDataTable(dbConn, commandType, commandText, parameters);
            }
        }

        public DataTable ExecuteDataTable(IDatabaseConnection databaseConnection, CommandType commandType, string commandText)
        {
            return ExecuteDataTable(databaseConnection, commandType, commandText, null);
        }

        public DataTable ExecuteDataTable(IDatabaseConnection databaseConnection, CommandType commandType, string commandText, List<IDbDataParameter> parameters)
        {
            using (IDbCommand cmd = databaseConnection.Connection.CreateCommand())
            {
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;

                AttachParameters(databaseConnection, cmd, parameters);

                IDbDataAdapter dataAdapter = _connectionProvider.CreateDataAdapter();
                dataAdapter.SelectCommand = cmd;

                DataSet dataSet = new DataSet();

                if (_logger.IsDebugEnabled)
                    _logger.Debug(string.Format("Filling Datatable with {0}", commandText));

                //Fill resultset from dataAdapter
                dataAdapter.Fill(dataSet);

                return dataSet.Tables[0];
            }
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(commandType, commandText, null);
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText, List<IDbDataParameter> parameters)
        {
            using (IDatabaseConnection dbConn = _connectionProvider.CreateDatabaseConnection())
            {
                if (dbConn.Connection.State != ConnectionState.Open)
                    dbConn.Connection.Open();

                return ExecuteNonQuery(dbConn, commandType, commandText, parameters);
            }
        }

        public int ExecuteNonQuery(IDatabaseConnection databaseConnection, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(databaseConnection, commandType, commandText, null);
        }

        public int ExecuteNonQuery(IDatabaseConnection databaseConnection, CommandType commandType, string commandText, List<IDbDataParameter> parameters)
        {
            using (IDbCommand cmd = databaseConnection.Connection.CreateCommand())
            {
                int result;

                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                AttachParameters(databaseConnection, cmd, parameters);

                if (_logger.IsDebugEnabled)
                    _logger.Debug(string.Format("Executing NonQuery {0} ", commandText));

                result = cmd.ExecuteNonQuery();
                
                return result;
            }
        }

       
        public int ExecuteNonQueryBatch(IEnumerable<IDbCommand> commands)
        {
            using (IDatabaseConnection databaseConnection = _connectionProvider.CreateDatabaseConnection())
            {
                if (databaseConnection.Connection.State != ConnectionState.Open)
                    databaseConnection.Connection.Open();

                if (_logger.IsDebugEnabled)
                    _logger.Debug("Starting a transaction");

                using (IDbTransaction transaction = databaseConnection.Connection.BeginTransaction())
                {
                    try
                    {
                        int result = 0;
                        foreach (IDbCommand command in commands)
                        {
                            using (IDbCommand cmd = databaseConnection.Connection.CreateCommand())
                            {
                                cmd.Transaction = transaction;
                                cmd.CommandText = command.CommandText;
                                cmd.CommandType = command.CommandType;
                                List<IDbDataParameter> paramList = new List<IDbDataParameter>();
                                foreach (IDbDataParameter param in command.Parameters)
                                {
                                    paramList.Add(param);
                                }
                                AttachParameters(databaseConnection, cmd, paramList);

                                if (_logger.IsDebugEnabled)
                                    _logger.Debug(string.Format("Executing {0} ", command.CommandText));

                                
                                    result = cmd.ExecuteNonQuery();
                               

                            }
                        }

                        if (_logger.IsDebugEnabled)
                            _logger.Debug("Committing the transaction");

                        transaction.Commit();

                        return result;
                    }
                    catch (Exception)
                    {
                        if (_logger.IsDebugEnabled)
                            _logger.Debug("Rolling back transaction");
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
        
        private void AttachParameters(IDatabaseConnection databaseConnection, IDbCommand command, IEnumerable<IDbDataParameter> commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            //If valid command parameters provided, then attaching given parameters to provided
            //command object.
            if (commandParameters != null)
            {
                command.Parameters.Clear();

                foreach (IDbDataParameter parameter in commandParameters)
                {
                    //attaching parameter to command object.
                    if ((parameter.Direction == ParameterDirection.InputOutput ||
                        parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    if (_logger.IsDebugEnabled)
                    {
                        object value;
                        if ((parameter.Value != null) && (parameter.Value.ToString().Length > 512))
                            value = parameter.Value.ToString().Substring(0, 512) + "...";
                        else
                            value = parameter.Value;

                        _logger.Debug(string.Format("Attaching {0} parameter with value {1}",
                                                     parameter.ParameterName, value));
                    }

                    command.Parameters.Add(parameter);
                }
            }
        }


    }
}
